﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace Trade.DBUtility.DataTableName
{
    public class CustomizableDesignDAL : MarshalByRefObject
    {
        public static string strConnectString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;

        #region 得到指定的数据集
        public static byte[] GetDataToByte(DataTableName DataTablename, bool bNeedSchema)
        {
            List<DataTableName> DataTableNameList = new List<DataTableName>();
            DataTableNameList.Add(DataTablename);
            try
            {
                return GetDataToByte(DataTableNameList, bNeedSchema);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }
        public static DataSet GetDataToDS(DataTableName DataTablename, bool bNeedSchema)
        {
            List<DataTableName> DataTableNameList = new List<DataTableName>();
            DataTableNameList.Add(DataTablename);
            try
            {
                return GetDataToDS(DataTableNameList, bNeedSchema);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }
        /// <summary>
        /// 得到指定的数据集
        /// </summary>
        public static byte[] GetDataToByte(List<DataTableName> DataTableNameList, bool bNeedSchema)
        {
            DataSet dsCustomizableDesignDAL = new DataSet("CustomizableDesignDAL");
            try
            {
                dsCustomizableDesignDAL = GetDataToDS(DataTableNameList, bNeedSchema);
                byte[] bytes = SerializerAndDeserialize.Serializer(dsCustomizableDesignDAL);
                bytes = ZipAndUnZip.ZipData(bytes);
                return bytes;
            }
            catch (Exception e)
            {
                throw (e);
            }
        }
        /// <summary>
        /// 得到指定的数据集
        /// </summary>
        public static DataSet GetDataToDS(List<DataTableName> DataTableNameList, bool bNeedSchema)
        {
            string strConn;

            DataSet dsCustomizableDesignDAL = new DataSet("CustomizableDesignDAL");
            strConn = strConnectString;

            SqlConnection sqlConn = new SqlConnection(strConn);
            SqlCommand sqlCom;
            SqlDataAdapter sqlDA;
            SqlParameter sqlParam;

            string strSQL;
            string strTableName;
            Type colType;
            try
            {
                DataTableNameList = SetSqlSelectParameterList(DataTableNameList);
                sqlConn.Open();
                //
                for (int i = 0; i < DataTableNameList.Count; i++)
                {
                    strSQL = DataTableNameList[i].strSelectSQL;
                    strTableName = DataTableNameList[i].strTableName;
                    sqlCom = new SqlCommand(strSQL, sqlConn);
                    if (DataTableNameList[i].SqlSelectParameterList != null)
                    {
                        for (int j = 0; j < DataTableNameList[i].SqlSelectParameterList.Count; j++)
                        {
                            sqlParam = DataTableNameList[i].SqlSelectParameterList[j];
                            sqlCom.Parameters.Add(sqlParam);
                        }
                    }
                    sqlDA = new SqlDataAdapter(sqlCom);
                    if (bNeedSchema)
                    {
                        sqlDA.FillSchema(dsCustomizableDesignDAL, SchemaType.Mapped, strTableName);
                    }
                    sqlDA.Fill(dsCustomizableDesignDAL, strTableName);
                    #region 主键赋上默认值
                    if (dsCustomizableDesignDAL.Tables[strTableName].PrimaryKey != null)
                    {
                        foreach (DataColumn dc in dsCustomizableDesignDAL.Tables[strTableName].PrimaryKey)
                        {
                            if (dc.ReadOnly)
                                continue;

                            colType = dsCustomizableDesignDAL.Tables[strTableName].Columns[dc.ColumnName].DataType;

                            if (colType == System.Type.GetType("System.String"))
                            {
                                dsCustomizableDesignDAL.Tables[strTableName].Columns[dc.ColumnName].DefaultValue = string.Empty;
                            }
                            else
                            {
                                if (colType == System.Type.GetType("System.Int16") || colType == System.Type.GetType("System.Int32") ||
                                   colType == System.Type.GetType("System.Int64") || colType == System.Type.GetType("System.Single") ||
                                   colType == System.Type.GetType("System.UInt16") || colType == System.Type.GetType("System.UInt32") ||
                                   colType == System.Type.GetType("System.UInt64") || colType == System.Type.GetType("System.Decimal") ||
                                   colType == System.Type.GetType("System.Double"))
                                {
                                    dsCustomizableDesignDAL.Tables[strTableName].Columns[dc.ColumnName].DefaultValue = 0;
                                }
                                else
                                {
                                    if (colType == System.Type.GetType("System.DateTime"))
                                    {
                                        dsCustomizableDesignDAL.Tables[strTableName].Columns[dc.ColumnName].DefaultValue = DateTime.Now;
                                    }
                                }
                            }
                        }
                    }
                    #endregion
                }

                return dsCustomizableDesignDAL;
            }
            catch (Exception e)
            {
                throw (e);
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
        }
        #endregion

        #region 保存数据集
        public static void SaveDataFromByte(byte[] bytes, DataTableName DataTablename)
        {
            List<DataTableName> DataTableNameList = new List<DataTableName>();
            DataTableNameList.Add(DataTablename);
            try
            {
                SaveDataFromByte(bytes, DataTableNameList);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }
        public static void SaveDataFromDS(DataSet dsData, DataTableName DataTablename)
        {
            List<DataTableName> DataTableNameList = new List<DataTableName>();
            DataTableNameList.Add(DataTablename);
            try
            {
                SaveDataFromDS(dsData, DataTableNameList);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }
        public static void SaveDataFromByte(byte[] bytes, List<DataTableName> DataTableNameList)
        {
            try
            {
                bytes = ZipAndUnZip.UnZipData(bytes);
                DataSet dsData = SerializerAndDeserialize.Deserialize(bytes) as DataSet;
                SaveDataFromDS(dsData, DataTableNameList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static void SaveDataFromDS(DataSet dsData, List<DataTableName> DataTableNameList)
        {
            string strConn;

            string strSQL;
            string strTableName;
            DataTableName.SaveAfterSQL SaveAftersql;

            SqlCommand sqlCom;
            SqlParameter sqlParam;

            SqlCommandBuilder CB;
            strConn = strConnectString;
            SqlConnection sqlConn = new SqlConnection(strConn);
            SqlTransaction sqlTrans = null;
            SqlDataAdapter sqlDA;
            List<SqlDataAdapter> sqlDAList = new List<SqlDataAdapter>();
            List<string> strTableNameList = new List<string>();
            try
            {
                DataTableNameList = SetSqlSelectParameterList(DataTableNameList);
                sqlConn.Open();
                //更新表,也就是DataSet
                for (int i = 0; i < DataTableNameList.Count; i++)
                {
                    strSQL = DataTableNameList[i].strSaveSQL;
                    if (strSQL == null || strSQL == string.Empty)
                        strSQL = DataTableNameList[i].strSelectSQL;
                    strTableName = DataTableNameList[i].strTableName;
                    //
                    sqlDA = new SqlDataAdapter(strSQL, sqlConn);
                    CB = new SqlCommandBuilder(sqlDA);

                    CB.ConflictOption = ConflictOption.OverwriteChanges;//.CompareRowVersion;
                    CB.SetAllValues = false;

                    sqlDA.InsertCommand = CB.GetInsertCommand();
                    sqlDA.DeleteCommand = CB.GetDeleteCommand();
                    sqlDA.UpdateCommand = CB.GetUpdateCommand();

                    sqlDAList.Add(sqlDA);
                    strTableNameList.Add(strTableName);
                }
                sqlTrans = sqlConn.BeginTransaction();
                for (int i = 0; i < sqlDAList.Count; i++)
                {
                    sqlDA = sqlDAList[i];
                    sqlDA.InsertCommand.Transaction = sqlTrans;
                    sqlDA.DeleteCommand.Transaction = sqlTrans;
                    sqlDA.UpdateCommand.Transaction = sqlTrans;
                }
                for (int i = 0; i < sqlDAList.Count; i++)
                {
                    strTableName = strTableNameList[i];
                    sqlDA = sqlDAList[i];
                    sqlDA.Update(dsData, strTableName);
                }
                //更新SQL语句，比如相关资料等，或者审批后处理库存等，相同事务中的SQL语句
                for (int i = 0; i < DataTableNameList.Count; i++)
                {
                    if (DataTableNameList[i].SaveAfterSQLList != null)
                    {
                        for (int j = 0; j < DataTableNameList[i].SaveAfterSQLList.Count; j++)
                        {
                            SaveAftersql = DataTableNameList[i].SaveAfterSQLList[j];
                            strSQL = SaveAftersql.strSQL;
                            //
                            sqlCom = new SqlCommand(strSQL, sqlConn, sqlTrans);

                            if (SaveAftersql.SqlParameterList != null)
                            {
                                for (int k = 0; k < SaveAftersql.SqlParameterList.Count; k++)
                                {
                                    sqlParam = SaveAftersql.SqlParameterList[k];
                                    sqlCom.Parameters.Add(sqlParam);
                                }
                            }
                            //
                            sqlCom.ExecuteNonQuery();
                        }
                    }
                }

                sqlTrans.Commit();
            }
            catch (Exception ex)
            {
                if (sqlTrans != null)
                    sqlTrans.Rollback();
                throw (ex);
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
        }
        #endregion
        #region 判断是否存在
        public static bool IsExist(DataTableName dataTableName)
        {
            int iCount;
            object objCount = null;
            string strConn;

            strConn = strConnectString;
            SqlConnection sqlConn = new SqlConnection(strConn);
            SqlCommand sqlCom;
            SqlParameter sqlParam;
            try
            {
                dataTableName = SetSqlSelectParameter(dataTableName);
                sqlConn.Open();
                sqlCom = new SqlCommand(dataTableName.strIsExistsSQL, sqlConn);
                if (dataTableName.SqlIsExistsParameterList != null)
                {
                    for (int j = 0; j < dataTableName.SqlIsExistsParameterList.Count; j++)
                    {
                        sqlParam = dataTableName.SqlIsExistsParameterList[j];
                        sqlCom.Parameters.Add(sqlParam);
                    }
                }
                objCount = sqlCom.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw (e);
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
            if (objCount == null)
                iCount = 0;
            else
            {
                iCount = objCount == DBNull.Value ? 0 : ((int)objCount);
            }
            return iCount > 0;
        }
        #endregion
        #region 执行SQL
        public static void ExecSQL(List<DataTableName> DataTableNameList)
        {
            string strConn;

            DataTableName.ExecAfterSQL DeleteAftersql;

            strConn = strConnectString;
            SqlConnection sqlConn = new SqlConnection(strConn);
            SqlTransaction sqlTrans = null;
            SqlCommand sqlCom;
            SqlParameter sqlParam;
            try
            {
                DataTableNameList = SetSqlSelectParameterList(DataTableNameList);
                sqlConn.Open();

                sqlTrans = sqlConn.BeginTransaction();
                for (int i = 0; i < DataTableNameList.Count; i++)
                {
                    //删除
                    sqlCom = new SqlCommand(DataTableNameList[i].strExecSQL, sqlConn);
                    sqlCom.Transaction = sqlTrans;
                    if (DataTableNameList[i].SqlExecParameterList != null)
                    {
                        for (int j = 0; j < DataTableNameList[i].SqlExecParameterList.Count; j++)
                        {
                            sqlParam = DataTableNameList[i].SqlExecParameterList[j];
                            sqlCom.Parameters.Add(sqlParam);
                        }
                    }
                    sqlCom.ExecuteNonQuery();
                }

                for (int i = 0; i < DataTableNameList.Count; i++)
                {
                    if (DataTableNameList[i].ExecAfterSQLList != null)
                    {
                        for (int j = 0; j < DataTableNameList[i].ExecAfterSQLList.Count; j++)
                        {
                            DeleteAftersql = DataTableNameList[i].ExecAfterSQLList[j];
                            string strSQL = DeleteAftersql.strSQL;
                            //
                            sqlCom = new SqlCommand(strSQL, sqlConn, sqlTrans);

                            if (DeleteAftersql.SqlParameterList != null)
                            {
                                for (int k = 0; k < DeleteAftersql.SqlParameterList.Count; k++)
                                {
                                    sqlParam = DeleteAftersql.SqlParameterList[k];
                                    sqlCom.Parameters.Add(sqlParam);
                                }
                            }
                            sqlCom.ExecuteNonQuery();
                        }
                    }
                }

                sqlTrans.Commit();
            }
            catch (Exception ex)
            {
                if (sqlTrans != null)
                    sqlTrans.Rollback();
                throw (ex);
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
        }
        #endregion
        #region 得到数据
        public static byte[] GetDataSetReportFromSQLToByte(string strSQL)
        {
            DataSet dsDataSetReportFromSQL = new DataSet("DataSetReportFromSQL");
            try
            {
                dsDataSetReportFromSQL = GetDataSetReportFromSQLToDS(strSQL);
                byte[] bytes = SerializerAndDeserialize.Serializer(dsDataSetReportFromSQL);
                bytes = ZipAndUnZip.ZipData(bytes);
                return bytes;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static DataSet GetDataSetReportFromSQLToDS(string strSQL)
        {
            string strManagerUserS = string.Empty;
            DataSet dsDataSetReportFromSQL = new DataSet("DataSetReportFromSQL");
            SqlConnection sqlConn = null;
            SqlCommand sqlCom;
            SqlDataAdapter sqlDA;
            string strGetDataSetReportFromSQL;
            strGetDataSetReportFromSQL = strSQL;
            try
            {
                sqlConn = new SqlConnection(strConnectString);
                sqlConn.Open();
                //
                sqlCom = new SqlCommand(strGetDataSetReportFromSQL, sqlConn);
                sqlDA = new SqlDataAdapter(sqlCom);
                sqlDA.Fill(dsDataSetReportFromSQL, "Tab_1");
                return dsDataSetReportFromSQL;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
            }
        }
        #endregion
        private static List<DataTableName> SetSqlSelectParameterList(List<DataTableName> DataTableNameList)
        {


            List<DataTableName> DataTableNameListReturn = new List<DataTableName>();
            foreach (DataTableName dtn in DataTableNameList)
            {
                DataTableName dtnreturn = new DataTableName();
                dtnreturn = SetSqlSelectParameter(dtn);
                DataTableNameListReturn.Add(dtnreturn);
            }
            return DataTableNameListReturn;
        }
        private static DataTableName SetSqlSelectParameter(DataTableName DataTablename)
        {
            DataTableName DataTableNameReturn = new DataTableName();
            DataTableNameReturn.strTableName = DataTablename.strTableName;
            DataTableNameReturn.strSelectSQL = DataTablename.strSelectSQL;
            DataTableNameReturn.strExecSQL = DataTablename.strExecSQL;
            DataTableNameReturn.strIsExistsSQL = DataTablename.strIsExistsSQL;
            DataTableNameReturn.strSaveSQL = DataTablename.strSaveSQL;

            if (DataTablename.SerSqlSelectParameterList != null)
            {
                foreach (DataTableName.SerSqlParameter pa in DataTablename.SerSqlSelectParameterList)
                {
                    SqlParameter param = pa.ToSqlParameter();
                    DataTableNameReturn.SqlSelectParameterList.Add(param);
                }
            }
            //
            if (DataTablename.SerSqlExecParameterList != null)
            {
                foreach (DataTableName.SerSqlParameter pa in DataTablename.SerSqlExecParameterList)
                {
                    SqlParameter param = pa.ToSqlParameter();
                    DataTableNameReturn.SqlExecParameterList.Add(param);
                }
            }
            //
            if (DataTablename.SerSqlIsExistsParameterList != null)
            {
                foreach (DataTableName.SerSqlParameter pa in DataTablename.SerSqlIsExistsParameterList)
                {
                    SqlParameter param = pa.ToSqlParameter();
                    DataTableNameReturn.SqlIsExistsParameterList.Add(param);
                }
            }
            //
            if (DataTablename.ExecAfterSQLList != null)
            {
                foreach (DataTableName.ExecAfterSQL execsql in DataTablename.ExecAfterSQLList)
                {
                    DataTableName.ExecAfterSQL execsql1 = new DataTableName.ExecAfterSQL();
                    execsql1.strSQL = execsql.strSQL;
                    //
                    foreach (DataTableName.SerSqlParameter pa in execsql.SerSqlParameterList)
                    {
                        SqlParameter param = pa.ToSqlParameter();
                        execsql1.SqlParameterList.Add(param);
                    }
                    DataTableNameReturn.ExecAfterSQLList.Add(execsql1);
                }
            }
            //
            if (DataTablename.SaveAfterSQLList != null)
            {
                foreach (DataTableName.SaveAfterSQL execsql in DataTablename.SaveAfterSQLList)
                {
                    DataTableName.SaveAfterSQL execsql1 = new DataTableName.SaveAfterSQL();
                    execsql1.strSQL = execsql.strSQL;
                    //
                    foreach (DataTableName.SerSqlParameter pa in execsql.SerSqlParameterList)
                    {
                        SqlParameter param = pa.ToSqlParameter();
                        execsql1.SqlParameterList.Add(param);
                    }
                    DataTableNameReturn.SaveAfterSQLList.Add(execsql1);
                }
            }
            return DataTableNameReturn;
        }
    }
}
